package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.tongji.run.manager.ManagerFactory;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.util.*;

public class 统计新浆员复采率 {



    public static String root = "E:\\文档\\2023\\考核记录\\程序生成\\新浆员";

    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3308/tongji?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "root", "123456");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});


        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        List tongjiList = new ArrayList();

        File rootFile = new File(root);
        rootFile.mkdirs();

        String day = "2023-01-01";
        String dateStrStart = day + " 00:00:00";
        String dateStrEnd = day + " 23:59:00";
        Integer days = 90;

        Date startDate = DateUtil.getDate(day,"yyyy-MM-dd",0);
        Date endDate = DateUtil.getDate(day,"yyyy-MM-dd",days - 1);

        List xinjiangyuankaoheList = new ArrayList();
        Map data = new HashMap();
        Integer i = 0;
        while (i < days ) {
            i = i + 1;
            Date xinjiangyuanDengjiDateStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1);
            Date xinjiangyuanDengjiDateEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1);

            Map xinjiangyuankaohe = new HashMap();
            xinjiangyuankaohe.put("day",DateUtil.format(xinjiangyuanDengjiDateStart,"yyyy/MM/dd"));
            //新浆员数
            List<HashMap> xinjiangyuanList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where d.firstCollectDate between ? and ? and r.registerDate between ? and ? and c.weight > 0 \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(xinjiangyuanDengjiDateStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuanDengjiDateEnd, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuanDengjiDateStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuanDengjiDateEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer xinjiangyuanCount = xinjiangyuanList.size();
            xinjiangyuankaohe.put("xinjiangyuanCount",xinjiangyuanCount);
            //新浆员14天复采量
            Date xinjiangyuan14fucaiDateRangeStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1+14);
            Date xinjiangyuan14fucaiDateRangeEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1+14);
            //新浆员数
            List<HashMap> xinjiangyuan14tianfucaiList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where d.firstCollectDate between ? and ? and r.registerDate between ? and ? and c.weight > 0 \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(xinjiangyuanDengjiDateStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuanDengjiDateEnd, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuan14fucaiDateRangeStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuan14fucaiDateRangeEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer xinjiangyuan14tianfucaiCount = xinjiangyuan14tianfucaiList.size();
            xinjiangyuankaohe.put("xinjiangyuan14tianfucaiCount",xinjiangyuan14tianfucaiCount);
            //新浆员20天复采量
            Date xinjiangyuan20fucaiDateRangeStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1+14);
            Date xinjiangyuan20fucaiDateRangeEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1+20);
            //新浆员数
            List<HashMap> xinjiangyuan20tianfucaiList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where d.firstCollectDate between ? and ? and r.registerDate between ? and ? and c.weight > 0 \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(xinjiangyuanDengjiDateStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuanDengjiDateEnd, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuan20fucaiDateRangeStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuan20fucaiDateRangeEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer xinjiangyuan20tianfucaiCount = xinjiangyuan20tianfucaiList.size();
            xinjiangyuankaohe.put("xinjiangyuan20tianfucaiCount",xinjiangyuan20tianfucaiCount);

            xinjiangyuankaoheList.add(xinjiangyuankaohe);
        }
        data.put("xinjiangyuankaoheList",xinjiangyuankaoheList);

        ManagerFactory.createExcel(root,
                "excelTemplates/员工/新浆员考核统计.xls",
                "\\" + DateUtil.format(startDate, "yyyy年MM月dd日") + "-" + DateUtil.format(endDate, "yyyy年MM月dd日") + ".xls",data);
    }
}
